package com.platform.utils;

import cn.hutool.core.collection.CollectionUtil;
import com.platform.exception.BaseException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtils {

    public static void exportUsers(String fileName, List<String> strList, HttpServletResponse response) {
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("导入模板");

        // 定义自定义表头
        List<String> customHeaders = new ArrayList<>();
        customHeaders.add("username");
        customHeaders.add("profile_picture");
        customHeaders.add("full_name");
        customHeaders.add("identity");
        customHeaders.add("sex");
        customHeaders.add("academy");
        customHeaders.add("major");
        customHeaders.add("email");

        // 创建表头行
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < customHeaders.size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(customHeaders.get(i));
        }

        // 为 "sex" 列添加下拉列表
        int sexColumnIndex = 4; // "sex" 列的索引（从0开始）
        String[] sexOptions = {"男", "女"};
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        CellRangeAddressList sexAddressList = new CellRangeAddressList(1, 100, sexColumnIndex, sexColumnIndex);
        DataValidationConstraint sexConstraint = validationHelper.createExplicitListConstraint(sexOptions);
        DataValidation sexValidation = validationHelper.createValidation(sexConstraint, sexAddressList);
        sexValidation.setSuppressDropDownArrow(!(sexValidation instanceof org.apache.poi.hssf.usermodel.HSSFDataValidation));
        sexValidation.setShowErrorBox(true);
        sheet.addValidationData(sexValidation);

        // 为 "identity" 列添加下拉列表
        int identityColumnIndex = 3; // "identity" 列的索引（从0开始）
        String[] identityOptions = {"学生", "教职工"};
        CellRangeAddressList identityAddressList = new CellRangeAddressList(1, 100, identityColumnIndex, identityColumnIndex);
        DataValidationConstraint identityConstraint = validationHelper.createExplicitListConstraint(identityOptions);
        DataValidation identityValidation = validationHelper.createValidation(identityConstraint, identityAddressList);
        identityValidation.setSuppressDropDownArrow(!(identityValidation instanceof org.apache.poi.hssf.usermodel.HSSFDataValidation));
        identityValidation.setShowErrorBox(true);
        sheet.addValidationData(identityValidation);


        try {
            // 设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment; filename=导入模板.xlsx");

            // 将工作簿写入响应输出流
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (IOException e) {
            throw new BaseException("excel导出异常");
        }


//        if (CollectionUtil.isEmpty(strList)) {
//            return;
//        }
//        XSSFWorkbook workBook = new XSSFWorkbook();
//        Sheet sheet = workBook.createSheet();
//        int rowIndex = 0;
//
//
//        Row row = sheet.createRow(rowIndex);
//        ServletOutputStream outputStream = null;
//        // 获取第一行表头
//        for (int i = 0; i < strList.size(); i++) {
//            Cell cell = row.createCell(i);
//            cell.setCellValue(strList.get(i));
//        }
//
//
//
//        try {
//            fileName = URLEncoder.encode(fileName, "UTF-8");
//            outputStream = response.getOutputStream();
//            response.setContentType("application/octet-stream;charset=UTF-8");
//            response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
//            workBook.write(outputStream);
//            outputStream.flush();
//            outputStream.close();
//        } catch (IOException e) {
//            throw new BaseException("excel导出异常");
//        }
    }

    /**
     * Excel数据获取
     *
     * @param file excel文件
     * @return 每个数据行对应一个Map，与键值对形式存储数据
     * @throws IOException
     */
    public static List<Map<String, Object>> getDataFromExcel(MultipartFile file) throws IOException {
        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        List<String> headers = new ArrayList<>();

        List<Map<String, Object>> data = new ArrayList<>();

        // 读取表头
        Row headerRow = sheet.getRow(0);
        for (Cell cell : headerRow) {
            // 去掉表头之后的括号
            headers.add(cell.getStringCellValue().replaceAll("\\(.*?\\)", ""));
        }

        // 读取数据行
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            Map<String, Object> rowData = new HashMap<>();
            for (int j = 0; j < headers.size(); j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    rowData.put(headers.get(j), "");
                    continue;
                }
                switch (cell.getCellTypeEnum()) {
                    case STRING:
                        rowData.put(headers.get(j), cell.getStringCellValue());
                        break;
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            rowData.put(headers.get(j), cell.getDateCellValue().toString());
                        } else {
                            rowData.put(headers.get(j), cell.getNumericCellValue());
                        }
                        break;
                    case BOOLEAN:
                        rowData.put(headers.get(j), cell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                        CellValue cellValue = evaluator.evaluate(cell);
                        switch (cellValue.getCellTypeEnum()) {
                            case STRING:
                                rowData.put(headers.get(j), cellValue.getStringValue());
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    rowData.put(headers.get(j), cell.getDateCellValue().toString());
                                } else {
                                    rowData.put(headers.get(j), cellValue.getNumberValue());
                                }
                                break;
                            case BOOLEAN:
                                rowData.put(headers.get(j), cellValue.getBooleanValue());
                                break;
                            default:
                                rowData.put(headers.get(j), "");
                                break;
                        }
                        break;
                    case BLANK:
                        rowData.put(headers.get(j), "");
                        break;
                    default:
                        rowData.put(headers.get(j), "未知类型");
                        break;
                }
            }
            data.add(rowData);
        }

        return data;
    }

}
